import pymysql
from SqlConfig.sqls import tree_sql


def getSubTypeInfo():
    """
    获取大小类的绑定信息和所属大类
    :return:
    """
    connection = getConnect()
    cursor = connection.cursor()
    query_sql_detail = '''
        SELECT
        a.phy_layer_id,
        a.type_id,
        b.type_name,
        b.unique_code,
        lower(c.phy_layer_tname),-- 图层的真实名称，
        d.type_name AS main_type_name ,
        d.unique_code as main_type_code
    FROM
        tc_event_subtype_phy_layer a
        LEFT JOIN tc_dic_event_any_type b ON a.type_id = b.type_id
        LEFT JOIN tc_phy_layer c ON c.phy_layer_id = a.phy_layer_id
        LEFT JOIN tc_dic_event_any_type d ON d.type_id = b.senior_id'''
    cursor.execute(query_sql_detail)
    all_rec = cursor.fetchall()
    connection.close()
    return all_rec


def getTreeInfo():
    connect = getConnect()
    cursor = connect.cursor()
    cursor.execute(tree_sql)
    result = cursor.fetchall()
    connect.close()
    return result


def getConnect():
    """
    获取MySQl的连接
    :return:
    """
    connection = pymysql.connect(
        host="127.0.0.1",
        database="test",
        user="root",
        password="egova",
        port=3306
    )
    return connection


if __name__ == '__main__':
    getSubTypeInfo()
